package com.tust.dao;

import com.tust.bean.TeachCourse;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


// 教师任课管理api
public class TeachCourseDao {

    // 声明一个连接对象引用 用来接收与数据库连接的connection实例对象
    private Connection connection;

    // 声明一个PreparedStatement对象引用
    private PreparedStatement statement;

    public TeachCourseDao(Connection connection) {
        // 获取到连接实例对象与数据库建立连接
        this.connection = connection;
    }


    /**
     * 添加授课信息
     * @param teachCourse
     * @return
     */
    public boolean insert(TeachCourse teachCourse) {

        try {

            // 检查是否已经存在此授课信息
            statement = connection.prepareStatement("SELECT id, is_delete FROM tea_course_relation WHERE teacher_id = ? AND course_id = ?");
            statement.setInt(1,teachCourse.getTid());
            statement.setString(2,teachCourse.getCid());
            try (ResultSet resultSet = statement.executeQuery()) { // 自动关闭ResultSet接口

                // 如果存在授课记录
                if (resultSet.next()) {

                    // 如果不是历史记录，打印请勿重复添加！
                    if(resultSet.getInt(2) == 0) {
                        System.out.println("该授课记录已存在，请勿重复添加！");
                        return false;
                    } else {
                        // 否则将此授课记录显示出来
                        statement = connection.prepareStatement("UPDATE tea_course_relation SET is_delete = 0 WHERE id = ?");
                        statement.setInt(1,resultSet.getInt(1));
                    }

                } else {
                    // 不存在 直接新增
                    statement = connection.prepareStatement("INSERT INTO tea_course_relation (teacher_id, course_id) VALUES (?, ?)");

                    statement.setInt(1, teachCourse.getTid());
                    statement.setString(2, teachCourse.getCid());
                }

                statement.executeUpdate();

                return true;

            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }


        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return false;
    }

    /**
     * 删除所有教师的所有授课记录
     * @return
     */
    public boolean delete() {

        try {
            statement = connection.prepareStatement("UPDATE tea_course_relation SET is_delete = 1 WHERE is_delete = 0");
            statement.executeUpdate();

            return true;
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return false;
    }

    /**
     * 删除指定教师的所有授课记录
     * @param tid
     * @return
     */
    public boolean delete(int tid) {

        try {
            statement = connection.prepareStatement("UPDATE tea_course_relation SET is_delete = 1 WHERE teacher_id = ? AND is_delete = 0");

            statement.setInt(1,tid);
            statement.executeUpdate();

            return true;
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return false;
    }

    /**
     * 删除指定教师的指定课程的授课记录
     * @param cid
     * @return
     */
    public boolean delete(String cid) {

        try {
            statement = connection.prepareStatement("UPDATE tea_course_relation SET is_delete = 1 WHERE course_id = ? AND is_delete = 0");
            statement.setString(1,cid);
            statement.executeUpdate();

            return true;
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return false;
    }

    /**
     * 更新授课信息
     * @param teachCourse
     * @return
     */
    public boolean update(TeachCourse teachCourse) {


        try {
            // 判断修改后的授课信息是否已经存在，如果存在就报错

            statement = connection.prepareStatement("SELECT id, is_delete FROM tea_course_relation WHERE teacher_id = ? AND course_id = ?");
            statement.setInt(1,teachCourse.getTid());
            statement.setString(2, teachCourse.getCid());
            try (ResultSet resultSet= statement.executeQuery()) {
                // 如果修改后的授课信息已经存在
                if(resultSet.next()) {
                    // 如果不是历史记录
                    if (resultSet.getInt(2) == 0) {
                        System.out.println("此授课信息已存在，修改失败！");
                        return false;
                    } else {
                        statement = connection.prepareStatement("UPDATE tea_course_relation SET teacher_id = ?, course_id = ?, is_delete = 0 WHERE id = ?");
                        statement.setInt(1,teachCourse.getTid());
                        statement.setString(2,teachCourse.getCid());
                        statement.setInt(3,resultSet.getInt(1));
                    }
                } else {
                    // 不存在，直接编辑
                    statement = connection.prepareStatement("UPDATE tea_course_relation SET teacher_id = ?, course_id = ? WHERE id = ? AND is_delete = 0");
                    statement.setInt(1,teachCourse.getTid());
                    statement.setString(2,teachCourse.getCid());
                    statement.setInt(3,teachCourse.getTcid());

                }

                statement.executeUpdate();
                return true;

            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }

        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return false;


/*        try {
            statement = connection.prepareStatement("UPDATE tea_course_relation SET teacher_id = ?, course_id = ? WHERE id = ? AND is_delete = 0");
            statement.setInt(1,teachCourse.getTid());
            statement.setString(2,teachCourse.getCid());
            statement.setInt(3,teachCourse.getTcid());
            statement.executeUpdate();
            return true;
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return false;*/
    }

    /**
     * 查询指定课程所对应的授课记录id
     * @param cid
     * @return
     */

    public List<Integer> query(String cid) {

        List<Integer> list=new ArrayList<>();
        try {
            statement = connection.prepareStatement("SELECT id FROM tea_course_relation WHERE course_id = ? AND is_delete = 0");
            statement.setString(1,cid);
            try (ResultSet resultSet = statement.executeQuery()) {

                while(resultSet.next()){
                    list.add(resultSet.getInt(1));
                }

            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return list;
    }

    /**
     * 查询指定教师的授课记录
     * @param tid
     * @return
     */
    public List<TeachCourse> query(int tid) {
        List<TeachCourse> list=new ArrayList<>();
        try {
            statement = connection.prepareStatement("SELECT tc.id, tc.teacher_id, t.name, tc.course_id, c.name FROM tea_course_relation tc JOIN teacher t ON tc.teacher_id = t.id JOIN course c ON tc.course_id = c.id WHERE tc.teacher_id = ? AND tc.is_delete = 0");
            statement.setInt(1,tid);
            try (ResultSet resultSet = statement.executeQuery()) {

                while(resultSet.next()){
                    TeachCourse teachCourse = new TeachCourse();
                    teachCourse.setTcid(resultSet.getInt(1));
                    teachCourse.setTid(resultSet.getInt(2));
                    teachCourse.setTname(resultSet.getString(3));
                    teachCourse.setCid(resultSet.getString(4));
                    teachCourse.setCname(resultSet.getString(5));
                    list.add(teachCourse);
                }

            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return list;
    }

    /**
     * 查询所有的授课记录
     * @return
     */
    public List<TeachCourse> query() {
        List<TeachCourse> list=new ArrayList<>();
        try {
            statement = connection.prepareStatement("SELECT tc.id, tc.teacher_id, t.name, tc.course_id, c.name FROM tea_course_relation tc JOIN teacher t ON tc.teacher_id = t.id JOIN course c ON tc.course_id = c.id WHERE tc.is_delete = 0");
            try (ResultSet resultSet= statement.executeQuery()) {

                while(resultSet.next()){
                    TeachCourse teachCourse = new TeachCourse();
                    teachCourse.setTcid(resultSet.getInt(1));
                    teachCourse.setTid(resultSet.getInt(2));
                    teachCourse.setTname(resultSet.getString(3));
                    teachCourse.setCid(resultSet.getString(4));
                    teachCourse.setCname(resultSet.getString(5));
                    list.add(teachCourse);
                }

            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return list;
    }


    /**
     * 关闭数据库连接中的statement 对象 释放相关资源
     */
    public void close() {

        try {
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}
